/** 
Copyright (c) 2010, Sheikh Abdul Wahid Ahmed
Details @ http://extremecodeworld.codeplex.com/license
**/

using System;
using EXtremecode.Utils;
using System.Data;
using  MySql.Data.MySqlClient;
using EXtremecode.DataAccessLayer; 
using System.Collections;
using System.Text;
using EXtremecode.Common;
namespace EXtremecode.DataAccessLayer
{
	/// <summary>
	/// Summary description for SQLServerDataAccessor.
	/// </summary>
	public class MySQLDataAccessor:DataAccessor
	{
        private string schemaName = string.Empty;
        #region SYSTEM QUERIES
		internal override string QUERY_Table 
		{ 
			get 
			{
                return string.Format(@"select TABLE_NAME as 'Name' from information_schema.tables
	                                        where Table_Schema = '{0}' and Table_Type = 'BASE TABLE'"
                    , schemaName);

			}
		}
        internal override string QUERY_View 
		{
			get 
			{
                return string.Format(@"select TABLE_NAME as 'Name' from information_schema.tables
	                                        where Table_Schema = '{0}' and Table_Type = 'VIEW'"
                    , schemaName);
			}
		}
		internal override string QUERY_TableColumn 
		{
			get 
			{
                return string.Format(@"select Table_Name,Column_Name as ColumnName,
		                                1 as TypeCode, CHARACTER_MAXIMUM_LENGTH as Length				
	                                 from information_schema.columns
                                     where Table_Schema = '{0}'"
                    , schemaName); 
			}
		}
		#endregion



        protected override DataTable GetRelationInfo()
        {
            string query = string.Format(@"
select rc.CONSTRAINT_SCHEMA as 'DatabaseName', rc.CONSTRAINT_NAME as 'FK_NAME'
	,rc.TABLE_NAME as 'FK_TABLE_NAME', rc.REFERENCED_TABLE_NAME as 'PK_TABLE_NAME'
    ,kcu.COLUMN_NAME as 'FK_COLUMN_NAME', kcu.REFERENCED_COLUMN_NAME as 'PK_COLUMN_NAME'
    
    from information_schema.referential_constraints rc
    inner join information_schema.key_column_usage kcu
    	on rc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
        	and rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
            and rc.TABLE_NAME = kcu.TABLE_NAME
            and rc.REFERENCED_TABLE_NAME = kcu.REFERENCED_TABLE_NAME    
     where rc.CONSTRAINT_SCHEMA = '{0}'"
                , schemaName);

            return doExecuteFillQuery(query,false);
        
        }

        

        public override string DBNow
        {
            get { return "NOW()"; }
        }
		
        public MySQLDataAccessor(string sAccessorName,string sConnectionURL):this(sAccessorName,sConnectionURL,true)
		{
			
		}

        public MySQLDataAccessor(string sAccessorName, string sConnectionURL, bool validationRequired)
            : base(sAccessorName, sConnectionURL,validationRequired)
        {
            schemaName = StringProcessor.GetValueFromConnetionString(sConnectionURL, "database");
        }

		public override string GetParameterName(string paramName)
		{
			return "?" + paramName;
		}
		public override string GetEnclosedDbObject(string dbObjectName)
		{
			return string.Format("`{0}`",dbObjectName);
		}
		public override IDbCommand CreateCommand()
		{
			
			return new MySqlCommand(); 
		}

        internal override IDbDataAdapter CreateDataAdapter()
		{
			MySqlDataAdapter adapter=new MySqlDataAdapter();
			adapter.RowUpdating +=new MySqlRowUpdatingEventHandler(Adapter_RowUpdating);
			adapter.RowUpdated +=new MySqlRowUpdatedEventHandler(Adapter_RowUpdated);
			return  adapter;
		}

		public override IDataParameter GetParameter(string name,string type)
		{
			MySqlParameter param=new MySqlParameter(name,(MySqlDbType)int.Parse(FreeHash.MYSQLDBType[type]),0); 
			return param;
		}
		

		
		protected override IDbConnection  CreateConnection()
		{
			MySqlConnection conn=new MySqlConnection(m_sConnectionURL);
			conn.StateChange +=new StateChangeEventHandler(base.Connection_StateChange);
			return conn;

		}

        private void Adapter_RowUpdating(object sender, MySqlRowUpdatingEventArgs e)
		{
            base.Adapter_RowUpdating(sender, e);
            e.Command.CommandText = e.Command.CommandText.Replace(";", "/*;") + "*/";

		}

		private void Adapter_RowUpdated(object sender, MySqlRowUpdatedEventArgs e)
		{
            base.Adapter_RowUpdated(sender, e);

            e.Command.CommandText = StringProcessor.RightTrim(e.Command.CommandText.Replace("/*;", ";")
                ,"*/");
            MySqlDataAdapter da = sender as MySqlDataAdapter;
            if ((e.Command == da.UpdateCommand || e.Command == da.InsertCommand) &&
                e.Errors == null)
            {
                MySqlCommand originalSelectCmd = da.SelectCommand;
                string originalCommandText = e.Command.CommandText;
                string[] currentSelectCommandsText = e.Command.CommandText.Split(';');

                if (currentSelectCommandsText.Length > 1
                    && currentSelectCommandsText[1].Trim() != string.Empty)
                {
                    e.Command.CommandText = currentSelectCommandsText[1];
                    try
                    {
                        using (MySqlDataAdapter daTemp = new MySqlDataAdapter())
                        {
                            daTemp.SelectCommand = e.Command;
                            using (DataTable dt = new DataTable())
                            {
                                daTemp.Fill(dt);
                                if (dt.Rows.Count > 0)
                                {
                                    DBUtil.CopyDataRow(dt.Rows[0], e.Row);
                                }
                            }
                        }
                    }
                    finally
                    {
                        da.SelectCommand = originalSelectCmd;
                        e.Command.CommandText = originalCommandText;
                    }
                }
            }
        }



        internal override string GeneratePagingQuery(string selectQuery, string sortedBy, bool isSortingTypeAscending, int pageNumber, int pageSize, long? totalRecords)
		{
            /*
             select SQL_CALC_FOUND_ROWS * from customers
                order by customerNumber
                limit 115,7; 
                select found_rows(); 
             */

           selectQuery = selectQuery.Insert(
                    selectQuery.IndexOf("Select", StringComparison.InvariantCultureIgnoreCase) + "Select".Length
                , "  SQL_CALC_FOUND_ROWS  ");

            string sortingType = (isSortingTypeAscending) ? " ASC " : " DESC ";
            long bottomRowNum = pageNumber * pageSize;
            long topRowNum = bottomRowNum - pageSize + 1;

            //paginated query.
            string peginatedQuery = string.Format(@"{0} order by {1} 
                   limit {2},{3}; ",
                selectQuery
                , sortedBy.Replace(",", sortingType + ",") + sortingType
                , topRowNum -1 // 0 based index
                , pageSize );



            return peginatedQuery;  
		}

	
       
    }
}
